import os
import pymysql
import threading


import sys
proj_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
sys.path.append(proj_path)


from config.Common import get_mysql_config
from config.ServerApp import app

# 创建一个线程安全的连接池
class MySQLConnectionPool:
    def __init__(self, mysql_config, max_connections=5):
        self.mysql_config = mysql_config
        self.max_connections = max_connections
        self.pool = threading.local()
        self.lock = threading.Lock()

    def get_connection(self):
        if not hasattr(self.pool, 'connection') or self.pool.connection is None:
            with self.lock:
                if not hasattr(self.pool, 'connection') or self.pool.connection is None:
                    # app.ctx.logger.info(f"Connecting to MySQL database: {self.mysql_config['host']}:{self.mysql_config['port']}")
                    self.pool.connection = pymysql.connect(
                        host=self.mysql_config['host'],
                        port=self.mysql_config['port'],
                        user=self.mysql_config['user'],
                        password=self.mysql_config['password'],
                        database=self.mysql_config['database'],
                        charset='utf8mb4',
                        autocommit=True
                    )
        return self.pool.connection

    def release_connection(self, conn):
        with self.lock:
            if self.pool.connection == conn:
                self.pool.connection.close()
                self.pool.connection = None

# 创建连接池实例
pool = MySQLConnectionPool(get_mysql_config())

def mysql_execute_sql(sql: str, params: tuple = None):
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        # app.ctx.logger.info(f"执行SQL: {sql}, params: {params}")
        if params is None:
            cursor.execute(sql)
        else:
            cursor.execute(sql, params)
        conn.commit()
        last_row_id = cursor.lastrowid
        return last_row_id
    except Exception as e:
        # app.ctx.logger.error(f"执行sql: {sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()

def mysql_insert_data(insert_sql: str, data: tuple):
    """
    插入单条数据到MySQL数据库
    :param insert_sql: 插入SQL语句
    :param data: 插入的数据元组
    :return: 插入记录的主键ID
    """
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        # app.ctx.logger.info(f"执行单条插入SQL: {insert_sql}, params: {data}")
        cursor.execute(insert_sql, data)
        conn.commit()
        last_row_id = cursor.lastrowid
        return last_row_id
    except Exception as e:
        # app.ctx.logger.error(f"执行sql: {insert_sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()

def mysql_batch_insert_data(insert_sql: str, data_list: list):
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        # app.ctx.logger.info(f"执行批量插入SQL: {insert_sql}, params: {data_list}")
        cursor.executemany(insert_sql, data_list)
        conn.commit()
    except Exception as e:
        # app.ctx.logger.error(f"执行sql: {insert_sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()


def mysql_insert_data(insert_sql: str, data: tuple):
    """
    插入单条数据到MySQL数据库
    :param insert_sql: 插入SQL语句
    :param data: 插入的数据元组
    :return: 插入记录的主键ID
    """
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        # app.ctx.logger.info(f"执行单条插入SQL: {insert_sql}, params: {data}")
        cursor.execute(insert_sql, data)
        conn.commit()
        last_row_id = cursor.lastrowid
        return last_row_id
    except Exception as e:
        # app.ctx.logger.error(f"执行sql: {insert_sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()


def mysql_execute_query(sql: str, params: tuple):
    conn = pool.get_connection()
    cursor = conn.cursor()
    try:
        # app.ctx.logger.info(f"执行查询SQL: {sql}, params: {params}")
        if params is None:
            cursor.execute(sql)
        else:
            cursor.execute(sql, params)
        data_list = cursor.fetchall()
        field_names = [description[0] for description in cursor.description]
        return [dict(zip(field_names, row)) for row in data_list]
    except Exception as e:
        # app.ctx.logger.error(f"执行sql: {sql} 失败: {str(e)}")
        conn.rollback()
        raise e
    finally:
        cursor.close()

# 释放连接
def release_connection():
    conn = pool.get_connection()
    pool.release_connection(conn)

# just for test
if __name__ == '__main__':
    # 插入测试数据的SQL语句
    insert_sql = '''
    INSERT INTO tasks (id, name, conv_id, scene, description, status, creator, modifier, gmt_create,gmt_modify,priority,task_start_time,task_end_time)
    VALUES (%s, %s, %s, %s, %s,%s, %s, %s, %s, %s,%s, %s, %s)
    '''


    from datetime import datetime
    now = datetime.now()

    data = ('11123', '测试任务', 'conv_id', '测试下任务', 'description',"INIT",'system','system',now,now,5,now,now)
    new_id = mysql_insert_data(insert_sql, data)

    # 查询测试
    query_sql = '''
    SELECT * FROM tasks
    WHERE status = %s AND id = %s
    '''
    result = mysql_execute_query(query_sql, ('INIT', '11123'))
    print(result)

    # 释放连接
    release_connection()
